/**
 * 
 */
package hr.mlab.app.expenses;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author nmustafic
 *
 */
public class DatabaseOpenHelper extends SQLiteOpenHelper {

	public static final String dbName = "mLabDB";
	public static final int    dbVersion = 1;
	public static final String dbCategories = "mLabCategories";
	public static final String dbGroups = "mLabGroups";
	public static final String dbGroupCategories = "mLabGroupCategories";
	public static final String dbTypes = "mLabTypes";
	public static final String dbTypeGroups = "mLabTypeGroups";
	public static final String dbTransactions = "mLabTransactions";
	public static final String dbPlans = "mLabPlans";
	
	public Context context;
	public DatabaseOpenHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
		this.context=context;
	}

	public DatabaseOpenHelper(Context context) {
		super(context, dbName, null, dbVersion); 
		this.context=context;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// Category
		db.execSQL("CREATE TABLE " + dbCategories + "(" +
				   "_id INTEGER PRIMARY KEY, " +
				   "categoryName TEXT NOT NULL);");
		
		// Group
		db.execSQL("CREATE TABLE " + dbGroups + "(" +
				   "_id INTEGER PRIMARY KEY, " +
				   "groupName TEXT NOT NULL);");

		// Connecting groups and categories
		db.execSQL("CREATE TABLE " + dbGroupCategories + "(" +
				   "categoryId INTEGER NOT NULL, " +
				   "groupId INTEGER NOT NULL, " +
				   "FOREIGN KEY (categoryId) REFERENCES " +
				   dbCategories + "(_id), " +
				   "FOREIGN KEY (groupId) REFERENCES " +
				   dbGroups + "(_id));");
		
		db.execSQL("CREATE UNIQUE INDEX idx_" + dbGroupCategories + " ON " + dbGroupCategories + "(categoryId, groupId);");

		// Type
		db.execSQL("CREATE TABLE " + dbTypes + "(" +
				   "_id INTEGER PRIMARY KEY, " +
				   "typeName TEXT NOT NULL, " +
				   "isIncome INTEGER NOT NULL);");
		
		// Connecting types and groups
		db.execSQL("CREATE TABLE " + dbTypeGroups + "(" +
				   "groupId INTEGER NOT NULL, " +
				   "typeId INTEGER NOT NULL, " +
				   "FOREIGN KEY (typeId) REFERENCES " +
				   dbTypes + "(_id), " +
				   "FOREIGN KEY (groupId) REFERENCES " +
				   dbGroups + "(_id));");
		
		db.execSQL("CREATE UNIQUE INDEX idx_" + dbTypeGroups + " ON " + dbTypeGroups + "(groupId, typeId);");

		// Transaction
		db.execSQL("CREATE TABLE " + dbTransactions + "(" +
				   "_id INTEGER PRIMARY KEY, " +
				   "groupId INTEGER NOT NULL, " +
				   "typeId INTEGER NOT NULL, " +
				   "comment TEXT, " +
				   "transactionYear NUMERIC(4,0) NOT NULL, " +
				   "transactionMonth NUMERIC(2,0) NOT NULL, " +
				   "transactionDay NUMERIC(2,0) NOT NULL, " +
				   "transactionAmount NUMERIC(15,4) NOT NULL, " +
				   "FOREIGN KEY (groupId) REFERENCES " +
				   dbGroups + "(_id), " +
				   "FOREIGN KEY (typeId) REFERENCES " +
				   dbTypes + "(_id));");
		
		db.execSQL("CREATE INDEX idx_" + dbTransactions + "ByDate ON " +
				dbTransactions + "(groupId, typeId, transactionYear, transactionMonth, transactionDay);");

		// Plan
		db.execSQL("CREATE TABLE " + dbPlans + "(" +
				   "_id INTEGER PRIMARY KEY, " +
				   "groupId INTEGER NOT NULL, " +
				   "typeId INTEGER NOT NULL, " +
				   "planYear NUMERIC(4,0) NOT NULL, " +
				   "planMonth NUMERIC(2,0) NOT NULL, " +
				   "planAmount NUMERIC(15,4) NOT NULL, " +
				   "FOREIGN KEY (typeId) REFERENCES " +
				   dbTypes + "(_id));");
		
		db.execSQL("CREATE INDEX idx_" + dbPlans + "ByDate ON " +
				   dbPlans + "(groupId, typeId, planYear, planMonth);");
		
		DbInitialization.Initdata(context, db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
}
